package cn.gson.crm.model.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import com.mysql.jdbc.StringUtils;

import cn.gson.crm.model.Jdbc;
import cn.gson.crm.model.po.StaffPo;

public class StaffPoDao {
	private int bands = 9;
//	/**
//	 * 获取全部咨询师的分配学员列表
//	 * 
//	 * @param type
//	 * @return
//	 * @throws SQLException
//	 */
//	public List<StaffPo> getStaffPoList() throws SQLException {
//		List<Map<String, Object>> listData = null;
//		String sql = "SELECT COUNT(allot.allot_id) as stsum,staff.staff_name,staff_id	FROM staff"
//				+ " LEFT OUTER JOIN allot ON staff.staff_id = allot.allot_staff_id"
//				+ " GROUP BY staff.staff_id ORDER BY stsum DESC;";
//		listData = Jdbc.getInstace().executeQueryForList(sql);
//		List<StaffPo> staffpoList = null;
//		if (listData != null && listData.size() > 0) {
//			staffpoList = new ArrayList<>();
//			for (Map<String, Object> data : listData) {
//				staffpoList.add(new StaffPo(Integer.parseInt(data.get("stsum").toString()), 
//						(String) data.get("staff_name"),
//						Integer.parseInt(data.get("staff_id").toString())));
//			}
//		}
//		return staffpoList;
//	}
//	
	/**
	 * 获取全部咨询师的分配学员列表
	 * 
	 * @param type
	 * @return
	 * @throws SQLException
	 */
	public List<StaffPo> getStaffPoListByType(int page ,String StaffName) throws SQLException {
		int start = (page - 1) * bands;
		List<Map<String, Object>> listData = null;
		String sql = "SELECT * FROM (SELECT s.staff_id, s.staff_name,"
				+ " (select count(1) from allot a where s.staff_id = a.allot_staff_id) stsum,"
				+ " (select count(1) from cooperation c where s.staff_id = c.cooperation_staff_id) cstsum"
				+ " FROM staff s) t WHERE (stsum > 0 or cstsum > 0)";
		List<Object> params = new ArrayList<>();
		if (!StringUtils.isNullOrEmpty(StaffName)) {
			sql += " AND staff_name LIKE ? ";
			params.add("%" + StaffName + "%");

		}
		sql += " ORDER BY stsum DESC LIMIT ? , ?;";
		params.add(start);
		params.add(bands);
		listData = Jdbc.getInstace().executeQueryForList(sql, params.toArray());
		List<StaffPo> staffpoList = null;
		if (listData != null && listData.size() > 0) {
			staffpoList = new ArrayList<>();
			for (Map<String, Object> data : listData) {
				staffpoList.add(new StaffPo(Integer.parseInt(data.get("stsum").toString()), 
						(String) data.get("staff_name"),
						Integer.parseInt(data.get("cstsum").toString()),
						Integer.parseInt(data.get("staff_id").toString())));
			}
		}
		return staffpoList;
	}
	
	/**
	 * 带条件获取咨询师的分页总数
	 */
	public int getStaffPageSum(String allotName) throws SQLException {
		String sql = "SELECT COUNT(*) as sum from (SELECT * FROM (SELECT s.staff_id,s.staff_name,"
				+ " (select count(1) from allot a where s.staff_id = a.allot_staff_id) stsum,"
				+ " (select count(1) from cooperation c where s.staff_id = c.cooperation_staff_id) cstsum"
				+ " FROM staff s) t WHERE (stsum > 0 or cstsum > 0)";
		List<Object> params = new ArrayList<>();
		sql = buildSqlAndParams(params, sql, allotName);
		sql += " ORDER BY stsum DESC) s";
		Map<String, Object> map = Jdbc.getInstace().executeQuery(sql, params.toArray());
		int sum = 0;
		if (map != null) {
			sum = Integer.parseInt(map.get("sum").toString());
			sum = sum % bands > 0 ? sum / bands + 1 : sum / bands;
		}
		return sum;
	}

	
	private String buildSqlAndParams(List<Object> params, String sql, String allotName) {
		if (!StringUtils.isNullOrEmpty(allotName)) {
			sql += " and staff_name like ? ";
			params.add("%" + allotName + "%");

		}
		return sql;
	}
	/**
	 * 根据咨询师id获取分配信息
	 * */
	public StaffPo getStaffPo(String staffId) throws SQLException{
		String sql = "SELECT COUNT(allot.allot_id) as stsum,staff.staff_name,staff_id	FROM staff"
				+ " LEFT OUTER JOIN allot ON staff.staff_id = allot.allot_staff_id"
				+ " where  staff.staff_id = ? GROUP BY staff.staff_id ORDER BY stsum DESC;";
		Map<String, Object>  data = Jdbc.getInstace().executeQuery(sql,staffId);
		StaffPo staffPo = new StaffPo();
		if(data != null){
			staffPo.setStaffId(Integer.parseInt(data.get("staff_id").toString()));
			staffPo.setStaffName((String) data.get("staff_name"));
			staffPo.setStudentSum(Integer.parseInt(data.get("stsum").toString()));
		}
		return staffPo;
	}
}
